/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.etop.gitAndBlog.dao;

import com.etop.gitAndBlog.model.GBException;
import com.etop.gitAndBlog.model.Message;
import com.etop.gitAndBlog.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Administrator
 */
public class MessageDao implements IMessageDao {

    private Connection con = null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;

    @Override
    public void add(Message message) {
        //先判断是否已经存在,如果loadByName没有抛出异常，说明用户存在
        try {
            loadByName(message.getName());
            throw new GBException("用户已存在");
        } catch (GBException e) {
        }
        con = DBUtil.getConnection();
        String sql = "INSERT INTO message values(null,?,?,?,?);";
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, message.getName());
            ps.setString(2, message.getNumber());
            ps.setString(3, message.getGit());
            ps.setString(4, message.getBlog());
            ps.executeUpdate();
            DBUtil.extracted(rs, ps, con);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void delete(int id) {
        //先判断是否存在,如果loadByName没有抛出异常，说明用户存在
        try {
            load(id);
        } catch (GBException e) {
            throw e;
        }
        try {
            con = DBUtil.getConnection();
            String sql = "delete from message where id = ?;";
            ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            ps.executeUpdate();
            DBUtil.extracted(rs, ps, con);
        } catch (SQLException ex) {
            Logger.getLogger(MessageDao.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void update(Message message) {
        //先判断是否存在,如果loadByName没有抛出异常，说明用户存在
        try {
            load(message.getId());
        } catch (GBException e) {
            throw e;
        }
        try {
            String sql = "update message set number=?,git=?,blog=? where id=?;";
            con = DBUtil.getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, message.getNumber());
            ps.setString(2, message.getGit());
            ps.setString(3, message.getBlog());
            ps.setInt(4, message.getId());
            ps.executeUpdate();
            DBUtil.extracted(rs, ps, con);
        } catch (SQLException ex) {
            Logger.getLogger(MessageDao.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public Message load(int id) {
        try {
            String sql = "select * from message where id = ?;";
            con = DBUtil.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                Message message = new Message(id, rs.getString("name"), rs.getString("number"), rs.getString("git"), rs.getString("blog"));
                DBUtil.extracted(rs, ps, con);
                return message;
            }
            DBUtil.extracted(rs, ps, con);
            throw new GBException("用户不存在");
        } catch (SQLException ex) {
            Logger.getLogger(MessageDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public Message loadByName(String name) {
        try {
            String sql = "select * from message where name = ?;";
            con = DBUtil.getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, name);
            rs = ps.executeQuery();
            if (rs.next()) {
                Message message = new Message(rs.getInt("id"), rs.getString("name"), rs.getString("number"), rs.getString("git"), rs.getString("blog"));
                DBUtil.extracted(rs, ps, con);
                return message;
            }
            DBUtil.extracted(rs, ps, con);
            throw new GBException("用户不存在");
        } catch (SQLException ex) {
            Logger.getLogger(MessageDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public List<Message> list() {
        try {
            List<Message> messageList = new ArrayList<Message>();
            con = DBUtil.getConnection();
            String sql = "select * from message;";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                Message message = new Message(rs.getInt("id"), rs.getString("name"), rs.getString("number"), rs.getString("git"), rs.getString("blog"));
                messageList.add(message);
            }
                DBUtil.extracted(rs, ps, con);
            if(messageList.isEmpty()){
                throw new GBException("没有用户");
            } else {
                DBUtil.extracted(rs, ps, con);
                return messageList;
            }
        } catch (SQLException ex) {
            Logger.getLogger(MessageDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public List<Message> listByNumber(String number) {
        try {
            List<Message> messageList = new ArrayList<Message>();
            con = DBUtil.getConnection();
            String sql = "select * from message where number like ?;";
            ps= con.prepareStatement(sql);
            ps.setString(1, number+'%');
            rs = ps.executeQuery();
            while(rs.next()){
                Message message = new Message(rs.getInt("id"), rs.getString("name"), rs.getString("number"), rs.getString("git"), rs.getString("blog"));
                messageList.add(message);
            }
                DBUtil.extracted(rs, ps, con);
            if(messageList.isEmpty()){
                throw new GBException("没有用户");
            } else {
                return messageList;
            }
        } catch (SQLException ex) {
            Logger.getLogger(MessageDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

}
